***********************************************************************
****************************** FIGURE 3B ******************************
***********************************************************************
preserve

drop if filer == 0 | nonRecon == 1
keep if flpdyr == 2019 | flpdyr == 2021
keep if single == 1

gen count = 1
capture drop temp*
gen tempFPL = hincpl
replace tempFPL = 401 if hincpl > 400
capture drop xtile*
bysort flpdyr: gen xtile = floor(tempFPL / 50)
replace xtile = xtile - 1 if tempFPL == 400

gen ratio_APTC_PTC = APTC / PTC
gen APTC_ratio = APTC / exch_income
gen PTC_ratio = PTC / exch_income

collapse (mean) APTC PTC APTC_ratio PTC_ratio ratio_APTC_PTC exch_income (sum) count [pw = wt], by(flpdyr xtile) fast

gen fpl_bin = ""
local i = 0
foreach bin in "0-49" "50-99" "100-149" "150-199" "200-249" "250-300" "300-350" "350-400" "400+" {
	replace fpl_bin = "`bin'" if xtile == `i'
	local i = `i' + 1
}

keep flpdyr xtile fpl_bin APTC PTC
order flpdyr xtile fpl_bin APTC PTC
sort flpdyr xtile

reshape wide APTC PTC, i(xtile) j(flpdyr)
order xtile fpl_bin APTC2019 PTC2019 APTC2021 PTC2021

local day = day(date(c(current_date), "DMY"))
if(`day' < 10) local day = ".0" + "`day'"
else local day = "." + "`day'"
local month = month(date(c(current_date), "DMY"))
if(`month' < 10) local month = ".0"+"`month'"
else local month = "." + "`month'"
local year = year(date(c(current_date), "DMY"))

capture mkdir "$workingDir/output/Figure_3/old"
capture copy "$workingDir/output/Figure_3/Figure_3B.C_Avg_PTCs.xlsx" "$workingDir/output/Figure_3/old/`year'`month'`day'_Figure_3B.C_Avg_PTCs.xlsx"

sort xtile
mkmat APTC2019 PTC2019 APTC2021 PTC2021, matrix(output)
putexcel set "$workingDir/output/Figure_3/Figure_3B.C_Avg_PTCs.xlsx", sheet("3B_avgs_data") modify
putexcel C1 = matrix(output), colnames
putexcel close

restore


***********************************************************************
****************************** FIGURE 3C ******************************
***********************************************************************
preserve

drop if filer == 0 | nonRecon == 1
keep if flpdyr == 2019 | flpdyr == 2021
keep if single == 1

gen count = 1
capture drop temp*
gen tempFPL = hincpl
replace tempFPL = 401 if hincpl > 400
capture drop xtile*
bysort flpdyr: gen xtile = floor(tempFPL / 50)

replace exch_income = 0 if exch_income < 25

gen ratio_APTC_PTC = APTC / PTC
gen APTC_ratio = APTC / exch_income
gen PTC_ratio = PTC / exch_income

collapse (mean) APTC_ratio PTC_ratio ratio_APTC_PTC (sum) count [pw = s006], by(flpdyr xtile) fast

gen fpl_bin = ""
local i = 0
foreach bin in "0-49" "50-99" "100-149" "150-199" "200-249" "250-300" "300-350" "350-400" "400+" {
	replace fpl_bin = "`bin'" if xtile == `i'
	local i = `i' + 1
}

keep xtile fpl_bin APTC_ratio PTC_ratio ratio_APTC_PTC flpdyr
keep if flpdyr == 2019 | flpdyr == 2021
reshape wide APTC_ratio PTC_ratio ratio_APTC_PTC, i(xtile) j(flpdyr)
order xtile fpl_bin APTC_ratio2019 PTC_ratio2019 ratio_APTC_PTC2019 APTC_ratio2021 PTC_ratio2021 ratio_APTC_PTC2021

sort xtile
mkmat APTC_ratio2019 PTC_ratio2019 ratio_APTC_PTC2019 APTC_ratio2021 PTC_ratio2021 ratio_APTC_PTC2021, matrix(output)
putexcel set "$workingDir/output/Figure_3/Figure_3B.C_Avg_PTCs.xlsx", sheet("3C_ratios_data") modify
putexcel C1 = matrix(output), colnames
putexcel close

restore


***********************************************************************
****************************** FIGURE 3D ******************************
***********************************************************************
preserve

drop if filer == 0 | nonRecon == 1
keep if flpdyr == 2019 | flpdyr == 2021
keep if single == 1

gen count = 1
capture drop temp*
gen tempFPL = hincpl
replace tempFPL = 401 if hincpl > 400
capture drop xtile*
bysort flpdyr: gen xtile = floor(tempFPL / 50)

collapse (sum) count [pw = s006], by(flpdyr xtile) fast

gen fpl_bin = ""
local i = 0
foreach bin in "0-49" "50-99" "100-149" "150-199" "200-249" "250-300" "300-350" "350-400" "400+" {
	replace fpl_bin = "`bin'" if xtile == `i'
	local i = `i' + 1
}

keep flpdyr xtile fpl_bin count
reshape wide count, i(xtile) j(flpdyr)
order xtile fpl_bin count2019 count2021 

mkmat count*, matrix(output)
putexcel set "$workingDir/output/Figure_3/Figure_3B.C.D_Avg_PTCs.xlsx", sheet("3D_share_data") modify
putexcel C1 = matrix(output), colnames
putexcel close

restore
